Shareware Grab Bag

home *** CD-ROM | disk | FTP | other *** search

/ Shareware Grab Bag / Shareware Grab Bag.iso / 003 / pcdbm.arc / MANUAL.1 < prev next >

Wrap

Text File | 1985-03-04 | 45KB | 2,113 lines

USER'S GUIDE FOR THE PC-DBMS (TM) DATA BASE MANAGEMENT SYSTEM version 1.0 (C) COPYRIGHT KWARE, 1985 Paul Kobrin Kware P.O. Box 16206 Arlington, Va. 22215 PC-DBMS DISTRIBUTION NOTICE PC-DBMS is distributed as user supported software. If you like it, I ask that you send a voluntary payment of $60 which will entitle you to a typeset copy of the User's Guide, a mailed copy of the next version of PC-DBMS without charge, support and my thanks. Please see appendix C for more information. In any event, you are encouraged to make unmodified copies of the complete set of distribution files for others. Commercial sale of PC-DBMS in any manner is prohibited without Kware's written permission but user groups and similar non-commercial organizations may impose their customary charges for copying, etc. Reproduction of the printed manual is prohibited. DISCLAIMER In no event will the author or supplier of this program be liable to you for any damages, including any lost profits, lost savings or other incidental or consequential damages arising from the use of or inability to use this program, even if the author or supplier has been advised of the possibility of such damages, or for any claim by any other party. PC-DBMS is a trademark of Kware, Inc. IBM is a trademark of International Business Machines Corporation. MS is a trademark of Microsoft Corporation. CONTENTS CHAPTER 1 INTRODUCTION CHAPTER 2 GETTING STARTED System Requirements . . . . . . . . . 2-1 Distribution Diskette . . . . . . . . 2-1 Installation . . . . . . . . . . . . 2-2 CHAPTER 3 HANDS ON TUTORIAL Starting the Program . . . . . . . . 3-2 Help . . . . . . . . . . . . . . . . 3-3 Command Entry and Editing . . . . . . 3-4 The Open Command . . . . . . . . . . 3-4 The Select Command . . . . . . . . . 3-5 The Show Command . . . . . . . . . . 3-8 The Remove Command . . . . . . . . . 3-10 The Display Command . . . . . . . . 3-10 The Define Command . . . . . . . . . 3-12 The Key and Dekey Commands . . . . . 3-15 The Exit Command . . . . . . . . . . 3-16 i CHAPTER 4 GENERAL Description of a PC-DBMS Data Base . 4-1 Limitations . . . . . . . . . . . . . 4-2 Data File Structure, Import and Export . . . . . . . . . 4-2 Object Names . . . . . . . . . . . . 4-3 The PC-DBMS Editor . . . . . . . . . 4-3 Command Formatting and Execution . . 4-4 Help . . . . . . . . . . . . . . . . 4-5 CHAPTER 5 COMMAND REFERENCE Overview . . . . . . . . . . . . . . 5-1 Notational Conventions . . . . . . . 5-2 Define . . . . . . . . . . . . . . . 5-2 Dekey . . . . . . . . . . . . . . . . 5-4 Display . . . . . . . . . . . . . . . 5-4 Exit . . . . . . . . . . . . . . . . 5-8 Key . . . . . . . . . . . . . . . . . 5-8 Open . . . . . . . . . . . . . . . . 5-9 Output . . . . . . . . . . . . . . . 5-9 Remove . . . . . . . . . . . . . . . 5-10 Select . . . . . . . . . . . . . . . 5-10 Show . . . . . . . . . . . . . . . . 5-17 Tolerance . . . . . . . . . . . . . 5-20 APPENDIX A KEYWORDS APPENDIX B THE RELATIONAL MODEL APPENDIX C USER SUPPORTED SOFTWARE INDEX ii CHAPTER 1 INTRODUCTION PC-DBMS is a data base management system built on the relational model. Its features include - Dynamically formatted full screen forms for data entry, retrieval and editing and for "fill in the blanks" query composition. - A query command that can retrieve selected records from one or two tables, optionally sorting them. - Dynamic files, i.e., the capability to create new, defined tables from query results. - A fast screen editor for command composition with previous command recall. The editor features word wrapping and flying reform. - A menu-based help system. - Files containing the data tables look just like the tables, facilitating import and export. - Indexing with automatic index update on data entry, deletion or modification. No operation requires existence of an index. 1-1 INTRODUCTION - Optional use of color. - Integrated data dictionary but kept in its own file. - Entire dbms contained in one EXE file. 1-2 CHAPTER 2 GETTING STARTED Please read through this section of the manual. Then go on to run the hands on tutorial in the next section. SYSTEM REQUIREMENTS PC-DBMS will run on IBM, Compaq and Columbia computers and similar systems under PC/MS-DOS version 1.1 or higher. Minimum memory is 192k. One disk drive is necessary. A second drive or hard disk is desirable. Output can be redirected to an optional printer. Color is vigorously supported but not required. PC-DBMS operates the video board directly. It will support IBM monochrome and color graphics boards and close copies of them. DISTRIBUTION DISKETTE The distribution diskette contains the files shown in Table 2-1. 2-1 GETTING STARTED DISTRIBUTION DISKETTE READ.ME printing instructions for this manual MANUAL.1 first half of this manual MANUAL.2 second half of this manual MUSIC.DEF a sample COMPOSER.DAT data base used OPUS.DAT in the tutorial PC-DBMS.EXE the dbms program REQUEST.DOC payment form and invoice Table 2-1. Files on distribution diskette. INSTALLATION The installation task copies the files on the distribution diskette necessary to run the tutorial onto another diskette or onto your hard disk. Then, store the distribution diskette as a back up. After running the tutorial from the copy, delete the tutorial's files to save space and continue using the copy; it will still contain everything needed to use PC-DBMS. If you do not have a hard disk, make a diskette copy of the distribution diskette as follows. Turn on your pc and load the operating system, i.e, boot it. You will need a formatted blank diskette. (The IBM Disk Operating System Manual describes booting and formatting.) Insert the distribution diskette in drive A:. If you have two drives, insert the blank diskette in drive B:. In any case, issue the following copy commands. COPY A:*.DEF B: COPY A:*.DAT B: COPY A:*.EXE B: 2-2 GETTING STARTED INSTALLATION If you have a one drive system, A: and B: refer to the source and distribution diskettes. DOS will tell you how to swap them in the drive to produce the copy after you issue each copy command. After making the copy, put the distribution diskette away and insert the copy in drive A:. You are ready to run the tutorial. If you have a hard disk and are running DOS 2.x, it is suggested but not required that you create a subdirectory for PC-DBMS. It is a convenient place to keep your data base files and the program. After booting your system, at the DOS prompt enter MD\DBMS and when the prompt returns, enter CD\DBMS. You now have a subdirectory called \DBMS and it is presently the default or current directory. Whether you created a subdirectory or not, copy the necessary files to your hard disk. These copy commands assume that the distribution diskette is in drive A:. If the diskette is in a different drive, substitute its designation in the commands. COPY A:*.DEF COPY A:*.DAT COPY A:*.EXE After making the diskette or hard disk copy, put the distribution diskette away. On a system without a hard disk, put the copy diskette in drive A:. You are ready to run the tutorial. After you run the tutorial, you might want to delete its sample data base from the hard disk or diskette copy. On a system without a hard disk, issue these delete commands. DEL A:*.DAT DEL A:*.DEF DEL A:*.IDX 2-3 GETTING STARTED INSTALLATION On a hard disk system, omit the A: in the preceding commands. The dbms program, PC-DBMS.EXE, will remain on the copy for your use. 2-4 CHAPTER 3 HANDS ON TUTORIAL This tutorial will acquaint you with the main capabilities of PC-DBMS in just a few pages. It is cumulative so if this is your first exposure to the tutorial, go through it in order. Within in an hour, you'll know the 80% of the program used 95% of the time. If you're already familiar with PC-DBMS, you might use the tutorial as a refresher. It is organized by command for convenience. The tutorial does not cover every command nor does it cover every feature and limitation of the commands it does cover. IF A CAPABILITY IS NOT MENTIONED, DO NOT ASSUME IT IS ABSENT. CHECK CHAPTER 4, COMMAND REFERENCE. To run the tutorial, you must have the program file PC-DBMS.EXE and the sample data base files (MUSIC.DEF, COMPOSER.DAT and OPUS.DAT) all on the disk in the logged drive. The tutorial permanently modifies the sample data base so be sure you are working with a copy of the distribution diskette. 3-1 HANDS ON TUTORIAL STARTING THE PROGRAM STARTING THE PROGRAM Boot your pc as usual. That is, start it and load the operating system. If you don't have a hard disk, insert the previously prepared PC-DBMS diskette (see INSTALLATION) into the default drive, typically A:. If you have a hard disk, make the subdirectory containing PC-DBMS the default, i.e., give the command CD\DBMS at the DOS prompt. At the DOS prompt (e.g., A>), type PC-DBMS and press return. The welcome screen appears showing the product name, version number, etc. Press any key. If your pc has a color video card, you will be asked if you also have a color monitor. Press Y or N. Now the main screen of PC-DBMS appears. At the top left is a wide window for you to enter commands. Right of it are function key descriptions. Two stripes the width of the screen are below the window. You'll see their function soon. The bottom half of the screen, the output window, displays data or help messages. The screen is illustrated in Figure 3-1. 3-2 HANDS ON TUTORIAL STARTING THE PROGRAM MMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMM : : F1=help : : F2=rmv help : COMMAND WINDOW : F3=prev cmd : : F7=execute : : F10=exit : : LMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMM MESSAGE WINDOW STRIPE COLUMN HEADINGS STRIPE --------------------------------------------------------- OUTPUT WINDOW --------------------------------------------------------- Figure 3-1. The main screen of PC-DBMS HELP Press the F1 key (top left of keyboard). The help menu's first page appears in the output window. Get help on "command entry/editing", the first menu item, by pressing the first function key, F1. Read the help text in the output window. The cursor returns to the command window. Try help on another topic if you like. 3-3 HANDS ON TUTORIAL COMMAND ENTRY AND EDITING COMMAND ENTRY AND EDITING Whatever you type is entered in the command window. Word wrapping automatically breaks lines so type the Gettysburg address without pressing carriage return. Four score and seven years ago, our fathers brought forth upon this continent a new nation conceived in liberty and dedicated to the proposition that all men are created equal .... Use the cursor keys (numeric keypad arrow keys) to position the cursor at the "s" in "seven". Now type "twenty-". Next, delete it by pressing the backspace key repeatedly. Press the return key. It doesn't initiate command execution; it never does. It breaks the line at "seven". Now execute the Gettysburg address like a command by pressing F7. This is folly so an error message is written onto the first stripe. That's what the stripe is for. What happens if you press F3? Try it. (Pressing F7 now will serve to clear the window.) THE OPEN COMMAND PC-DBMS sessions usually begin with the open command. It makes an existing data base available or starts the process of creating a new data base. Let's open the sample data base MUSIC. Type OPEN MUSIC (upper or lower case) in the command window and press F7 to execute the command. The message window should read "Database MUSIC opened." 3-4 HANDS ON TUTORIAL THE SELECT COMMAND THE SELECT COMMAND The select command retrieves data from the data base, displaying it on screen or writing it to a file. The simplest select command is only two words. Type SELECT COMP.ALL and press F7. Figure 3-2 shows the result as displayed in the output window. NAME BORN DIED COUNTRY Tchaikovsky, P. 1840 1893 Russia Brahms, J. 1833 1897 Germany Verdi, G. 1813 1901 Italy Handel, G. 1685 1759 England Mozart, W. 1756 1791 Austria Bach, J. 1685 1750 Germany Chopin, F. 1810 1849 France Beethoven, L. 1770 1827 Germany Haydn, F. 1732 1809 Austria Mendelssohn, F. 1809 1847 Germany Debussy, C. 1862 1918 France Figure 3-2. Result of SELECT COMP.ALL The data base contains the table COMP whose entire contents are now in the output window. The table is stored in its own file. The table has eleven lines called records and four data columns called fields. Suppose we want only composers born no earlier than 1700. Press the F3 key to recall the first select command. Press the numeric keypad's END key to place the cursor at the command's end. Press the space bar once and type WHERE BORN => 1700 so that SELECT COMP.ALL WHERE BORN => 1700 is in the command window. Press F7 to execute it. The where clause can have up to ten conditions. Recall the previous command, position the cursor and add AND COUNTRY = "Germany" so that the entire command is: SELECT COMP.ALL WHERE BORN =>1700 AND COUNTRY = "Germany". This command has two conditions. Press F7. 3-5 HANDS ON TUTORIAL THE SELECT COMMAND Note that text strings are enclosed in quote marks. We could connect the conditions with OR in place of AND. That would change the query's meaning. Let's retrieve the same data alphabetized. Recall the previous command, position the cursor at its end, add SORTED BY NAME and press F7. Note that a where clause, if present, precedes the sort clause. It's possible to choose which fields are displayed and their order. Recall the previous command and edit it to read SELECT COMP.BORN NAME COUNTRY by using the cursor keys, backspace and the delete key (Del). Execute the command. The MUSIC data base also contains the table OPUS. PC-DBMS permits its contents to be displayed along with the contents of COMP. Execute SELECT COMP.BORN NAME OPUS.ALL. Some output will scroll off screen and what's left is shown in Figure 3-3. BORN NAME COMPOSER WORK 1685 Handel, G. Beethoven, L. Symphony No. 5 1756 Mozart, W. Brahms, J. Lullaby 1756 Mozart, W. Beethoven, L. Symphony No. 5 1685 Bach, J. Brahms, J. Lullaby 1685 Bach, J. Beethoven, L. Symphony No. 5 1810 Chopin, F. Brahms, J. Lullaby 1810 Chopin, F. Beethoven, L. Symphony No. 5 1770 Beethoven, L. Brahms, J. Lullaby 1770 Beethoven, L. Beethoven, L. Symphony No. 5 1732 Haydn, F. Brahms, J. Lullaby 1732 Haydn, F. Beethoven, L. Symphony No. 5 1809 Mendelssohn, F. Brahms, J. Lullaby 1809 Mendelssohn, F. Beethoven, L. Symphony No. 5 1862 Debussy, C. Brahms, J. Lullaby 1862 Debussy, C. Beethoven, L. Symphony No. 5 Figure 3-3. Result of SELECT COMP.BORN NAME OPUS.ALL 3-6 HANDS ON TUTORIAL THE SELECT COMMAND The composers' birth years and names from COMP were paired with their names and works from OPUS. Unfortunately, Debussy's vital statistics were paired with a Beethoven symphony and so forth. Add the following clause to the command to match composers only with their own works: WHERE COMPOSER = COMP.NAME. Execute the corrected command. Linking related records from different tables is called a join. Records from the two tables are joined together only when they have the same composer name, that is, WHERE OPUS.COMPOSER = COMP.NAME. Let's look again at the join command: SELECT COMP.BORN NAME OPUS.ALL WHERE COMPOSER = COMP.NAME. The first occurrence of "COMP." (in COMP.BORN) makes COMP the default table. Subsequent field names not preceded by a table name (NAME) are presumed to be from COMP. When OPUS.ALL is encountered, the default table changes to OPUS. Hence, COMPOSER is assumed to mean OPUS.COMPOSER. A field name explicitly qualified by a table name such as COMP.BORN is called a qualified field name. You can qualify any field name; the default capability is a convenience to save typing. Sometimes a select command retrieves so many records that they start to scroll off screen. You can freeze the action by pressing control-S (hold the control key and press "s" once). Then press any key to resume scrolling. However, to terminate retrieval prematurely, press F10. You should know about one last select capability. The result of the select command can become a table in the data base rather than a display on the screen. Just append a clause like INTO NU_TABLE to the end of the select command. Then you can run queries against NU_TABLE or whatever name you give the new table. Try the previous select command with the clause INTO JOIN_TBL. Execute another select command against the JOIN_TBL to confirm its existence. (THIS EXERCISE WILL 3-7 HANDS ON TUTORIAL THE SELECT COMMAND PERMANENTLY ALTER THE SAMPLE DATA BASE SO BE SURE YOU ARE WORKING WITH A COPY.) One reason to save the query result in a table is to do a three way join such as to join together tables A, B and C. The select command accepts no more than two table names before the into clause. So use a select command to join A and B INTO X. Then use a second select command to join X and C. THE SHOW COMMAND So far we have discovered two tables in the MUSIC data base. Are there others? Type SHOW and press F7. The result is in Figure 3-4. In fact, there are 3 tables. TABLE NAME FILE RECORDS FIELDS EOR MODIFIED COMP COMPOSER.DAT 11 6 2 11-25-1984 OPUS OPUS.DAT 2 2 2 11-25-1984 JOIN_TBL JOIN_TBL.DAT 2 4 2 12-15-1984 Figure 3-4. Result of SHOW The recall and default table capabilities and the command editor save typing. Keyword truncation saves typing, too. Any keyword longer than 3 characters can be truncated to 3 or more characters. For example, SHO has the same meaning as SHOW. Try it! Keywords are the boilerplate of commands. Examples are select, show, open and into. The names of data bases (MUSIC), tables (COMP) and fields (BORN) are not keywords. Neither are field values such as "Germany". Don't truncate names and values, only keywords. The show command can retrieve additional information about the tables in the data base. Try SHO\DESCRIBE. It brings up verbal descriptions of the tables. 3-8 HANDS ON TUTORIAL THE SHOW COMMAND Show can also retrieve information about fields. Execute SHO\FIELDS but press control-S before any output scrolls off screen. When ready, press any key to resume scrolling. The entire result is shown in Figure 3-5. FIELD NAME TYPE KEY FROM TO TABLE COMP NAME T N 1 19 BORN N N 20 23 filler1 F N 24 24 DIED N N 25 28 filler2 F N 29 29 COUNTRY T N 30 44 TABLE OPUS COMPOSER T N 1 19 WORK T N 20 44 TABLE JOIN_TBL BORN N N 1 4 NAME T N 5 23 COMPOSER T N 24 42 WORK T N 43 67 Figure 3-5. Result of the command SHO\FIELDS Consider COMP. It has two fields, filler1 and filler2, which have been hidden so far. That's because they are filler or F type fields. The fields which select has displayed are either text (T) or numeric (N). All the fields in MUSIC are not KEYed (N), i.e., there are no indices yet. The last two columns give the beginning and ending character positions in the file for each field. 3-9 HANDS ON TUTORIAL THE SHOW COMMAND Finally, verbal descriptions of fields can be retrieved. Execute SHO\FIE\DES. Any of the four show command forms can be followed by one or more table names. Then, only the information for the indicated table(s) will be retrieved. For example, try SHO\FIE COMP. The show command displays the contents of the data dictionary, known as the schema. PC-DBMS uses the schema internally. THE REMOVE COMMAND The table JOIN_TBL you created with a select command keeps appearing when show commands are executed. Let's remove it from the schema. Execute REMOVE JOIN_TBL and try a show command to confirm the deletion. The file JOIN_TBL.DAT which contained the table is not deleted. Only the schema is altered. THE DISPLAY COMMAND The display command invokes the display utility to add, delete or modify records. The utility automatically creates a full screen data entry form. [THE FOLLOWING EXERCISES WILL PERMANENTLY MODIFY THE SAMPLE DATA BASE. BE SURE YOU ARE USING A COPY.] Suppose we want to add a record to the COMP table. Execute DISPLAY/INPUT COMP and watch the data entry screen appear. Note that INPUT appears at the bottom right. Field names are placed next to blocks for field values. 3-10 HANDS ON TUTORIAL THE DISPLAY COMMAND The display utility has its own help; press F1. After you read the help text on screen, press any key to restore the data entry form. Let's enter a record for another composer. In the block marked NAME, type Dvorak, A. Then press tab. The cursor now jumps to the BORN block. Enter 1841 and press tab. Enter the entire four field record: Dvorak, A. 1841 1904 Czechoslovakia If you entered a field incorrectly, return to it by pressing F9 or tab repeatedly. Then type over the erroneous entry. The cursor keys will move you around within each field block. Try editing even if you didn't make an error. Press F7 to add this record to the COMP table. The record will be stored if the two numeric fields contain numbers. If there is a defective entry for a number, an error message will appear on the bottom line and the cursor will be placed in the offending field block for you to make a correction. After adding a record, the field blocks are cleared so you can enter another record. For now, press F10 to leave the display utility and return to the main screen. If you like, confirm the addition of Dvorak's record by executing SELECT COMP.ALL. Now, let's use the display utility to fix the error in Beethoven's record. Execute DIS\EDIT COMP and note that the same data entry screen reappears except now it shows QUERY at the bottom right rather than INPUT. First, we have to find Beethoven's record. Then we have to edit and store it. The QUERY screen finds records which are then displayed on the EDIT screen. 3-11 HANDS ON TUTORIAL THE DISPLAY COMMAND Beethoven was born in 1770 so we will find all records containing a birth year of 1770. This will bring up Beethoven's record and possibly others. Tab to the BORN field block on the QUERY screen and enter 1770. Press F7 to start the search for 1770 records. At the bottom right, QUERY changes to EDIT and a 1770 record appears. It's Beethoven's record. Tab to the COUNTRY block. Beethoven lived mainly in Austria, not Germany. Make the change by typing over the incorrect entry. Press F6 to save the corrected record. If you wanted to delete the record, you could press F4 instead. Or you could press F7 to leave it intact. Here are the choices: F4 = delete record \ F6 = change record to edited one > & display next record F7 = leave unchanged / F10 = leave unchanged, return to QUERY screen F4, F6 and F7 cause display of the next 1770 record, if there is one. However, Beethoven's is the only 1770 record so the screen clears and a message "No more records" appears. Press any key. The QUERY screen reappears. You can now repeat the cycle of finding records (QUERY screen) and correcting them (EDIT screen). From the QUERY screen, you can exit to the main screen by pressing F10. Do so. THE DEFINE COMMAND The define command invokes the define utility which adds definitions of tables and their fields to the schema. The utility consists of two prompting screens. The first screen is used to describe a table. The second screen is for describing a field. 3-12 HANDS ON TUTORIAL THE DEFINE COMMAND Let's define a new table with two fields. Execute DEFINE. The first of the prompting screens replaces the main screen. It asks a sequence of four questions about the new table. Figure 3-6 shows the screen with all questions answered. Complete your screen similarly. Use the backspace and cursor keys for editing. The second screen appears when you press return after the last question. --------------------------------------------------------- DESCRIPTION OF NEW TABLE Name of table: ORCHESTRAS File spec of file to hold ORCHESTRAS: ORCHSTRA.TXT Enter description of table (optional): INFORMATION ABOUT SOME ORCHESTRAS Enter code for record terminator: 2 0 Nothing 1 Carriage return 2 Carriage return- line feed F10 to quit --------------------------------------------------------- Figure 3-6. The define utility's first or table definition screen The second screen asks a sequence of five questions about the new tables's first field. Figure 3-7 shows the screen with all questions answered. Complete your screen similarly. 3-13 HANDS ON TUTORIAL THE DEFINE COMMAND --------------------------------------------------------- DESCRIPTION OF FIELD Name of field: ORCHESTRA Data Type Choices Enter first letter of data type: T Text Number Filler How wide in characters is field ORCHESTRA? 25 Index field (Y/N)? N Enter description of field (optional): THE ORCHESTRA'S NAME, E.G., CHICAGO SYMPHONY F10 to stop defining fields --------------------------------------------------------- Figure 3-7. The define utility's second or field definition screen When you press return after the last question, the same field definition screen restarts so you can define the second field. Complete it. Here are the five questions and answers for the second field. FIELD NAME CONDUCTOR DATA TYPE T FIELD WIDTH 19 INDEX N VERBAL DESCRIPTION Last name, first name of music director 3-14 HANDS ON TUTORIAL THE DEFINE COMMAND The field definition screen is now prompting for another field's description but we're finished. To terminate the field definition cycle, press F10. Now the first or table definition screen reappears. You could define another table and its fields but we're done. Press F10 to exit the definition utility and restore the main screen. Try SHO\FIELDS ORCHESTRAS to confirm addition of the new table to the schema. If you want, add records to the table with DIS\INPUT ORCHESTRAS. THE KEY AND DEKEY COMMANDS PC-DBMS can index a field. The index or key works like a book's index; it speeds searches. In addition, a key can provide a sorted retrieval without doing a sort. You can tell if a field is keyed from the KEY column in the SHOW\FIELD command's output. See Figure 3-5. If a field is keyed, its index will be updated whenever a value in that field is added, deleted or changed by the display utility. Index updating slows file maintenance and indices use disk space so only certain fields should be keyed. A field starts life without indexing if created by the SELECT ... INTO command. It may start out indexed if created by the define utility. See Figure 3-7's fourth question. If a field does not already have an index, you can give it one with the key command. Execute KEY COMP.NAME. Confirm the indexing with SHOW/FIE COMP. Try SELECT COMP.ALL SORTED BY NAME. The retrieval will be sorted and a little faster than without the key. The speed up is dramatic on tables with many records. 3-15 HANDS ON TUTORIAL THE KEY AND DEKEY COMMANDS You can remove a key with the dekey command. Execute DEKEY COMP.NAME. Now try SELECT COMP.ALL SORTED BY NAME. Did you notice the slow down? Indexing is especially important in joins. Consider SELECT COMP.ALL OPUS.WORK WHERE OPUS.COMPOSER = COMP.NAME AND BORN > 1800. The join condition, OPUS.COMPOSER = COMP.NAME, will process much more quickly if at least one of the two fields is keyed. The COMP.BORN condition also will process more quickly if that field is keyed. However, the where clause exploits no more than one index per table. Hence, keys will be most used if they're on fields in different tables, e.g., on OPUS.COMPOSER and COMP.BORN. Indexing is never necessary in PC-DBMS. It only accelerates processing, sometimes dramatically, though. THE EXIT COMMAND There is an exit command but the simple way to execute it is to press F10. You're now back at the DOS level and done with PC-DBMS. 3-16 CHAPTER 4 GENERAL DESCRIPTION OF A PC-DBMS DATA BASE A PC-DBMS data base consists of three kinds of files. The data base definitions (schema) reside in one file that has a "DEF" extension. Its file name is the name of the data base. This file must exist. The schema file must be in the current directory of the logged drive. The data of a data base are in named tables that have records and fields. Each table resides in its own file. Nothing else is in these files. The name of the file may be different from the name of the table it contains. There may be any number of data files associated with a data base. Indices are in the third type of file. There may be zero or one index files for each data file. Each index file holds the indices for all the keyed fields of the corresponding data file. The disk drive and file name of the index file are the same as the drive and file name of the corresponding data file. However, the index file's extension is IDX. 4-1 GENERAL LIMITATIONS LIMITATIONS Each table may have up to 32767 records including deleted records. Each record may have up to 999 fields. The schema file holds up to 32767 records. It uses one record for each table and another for each field. Deleting definition records (with the remove command) does not reduce the count. DATA FILE STRUCTURE, IMPORT AND EXPORT Each data file contains the entirety of one table and nothing else. The file is an ASCII representation of the table. That is, there is no packing or data compression and numbers are represented as text, not in binary or other format. The use of ASCII files to represent tables facilitates import and export of tables between PC-DBMS and other applications. The show\fields command is used to obtain the definition of a table for export. Use the define utility to describe an imported table to PC-DBMS. Indices may be unreliable if an indexed table is altered by another program. Indices should be rebuilt. Also, record counts exhibited by the show command are not reliable on tables supplied or altered externally. However, the erroneous record count is innocuous. PC-DBMS flags a deleted record by placing a backslash (\) in the first character position. 4-2 GENERAL OBJECT NAMES OBJECT NAMES The names of data bases, tables and fields consist of alphanumeric characters and the underscore (_). The first character of a name must be alphabetic. Do not use PC-DBMS's keywords as names. See Appendix A. Although PC-DBMS will accept non-conforming names, the consequences are unpredictable. Case does not matter. Data base names are 1-8 characters. Table and field names are 1-16 characters. However, tables created by the select ... into command are limited to 1-8 character names. Most commands use "qualified field names." A qualified field name is a field name preceded by the name of its table and a period. For example, COMP.BORN is the field BORN from the table COMP. THE PC-DBMS EDITOR Upon invoking the program, its main screen will appear. A framed window is at the top left. Commands are typed into it. A word processing type editor operates in the command window. The same editor operates in the field blocks of the data entry form created by the display command. The following describes the editor. There are two modes, insert and replace. The default mode is insert in the command window. The cursor is "low" in this mode. Pressing the "Ins" key toggles between the modes. In the display utility, only the replace mode is available and the cursor is "high." In insert mode, each entered character pushes those after it rightward. If a word would be pushed out of the display area, a flying reform occurs. That is, the last word is put at the beginning of the next line (word wrap) and all text that was on that line is pushed right which may cause word wrap and reform of the following line and 4-3 GENERAL THE PC-DBMS EDITOR so forth. In replace mode, an entered character replaces the one that was at the cursor. The following applies to both modes. The delete key deletes the character under the cursor and the backspace key deletes the preceding character. The line is closed up in either case by shifting subsequent characters left. The four cursor keys function. The Home and End keys move the cursor to the beginning and end of the current line. The Page Up key moves the cursor to the first position in the window or field block. Carriage return breaks a line at the cursor. That is, all characters at and after the cursor are shifted to the next line. A flying reform will occur. (Carriage return does not initiate command execution.) COMMAND FORMATTING AND EXECUTION Commands may spread over as many lines as necessary. There is no continuation character. Words or items in a command are separated by one or more spaces. The space is the only separator recognized by PC-DBMS. Numbers may be preceded by a sign and may contain a decimal point. They may be followed immediately by "E" or "e" and a signed or unsigned integer to obtain scientific representation. For example, VELOCITY > 3.35E+11. 4-4 GENERAL COMMAND FORMATTING AND EXECUTION Character strings must be entered in quotes. For example, NAME = "Smith". Use a pair of quotes to embed a quote mark in a string. For example, NAME = "Herman ""Babe"" Ruth". Comments, which are ignored by PC-DBMS, may be placed in commands. They are preceded by an apostrophe ('). A comment ends a command. PC-DBMS keywords may be entered in upper, lower or mixed case. They may be truncated to three or more characters. The F3 key clears the window and puts the previous command into it for possible editing. The F7 key executes the command in the window. (Carriage return is used for editing only.) If the command is flawed, an error or warning message will be issued. Command execution is aborted if there is an error. If only a warning is given, execution will proceed. Most messages are errors. The F10 key executes the exit command, terminating the program and returning the user to the operating system. HELP A help facility is available in the main screen. Press F1 to obtain the first page of a multi-page menu of help topics. The menu and cursor appear in the bottom half of the main screen, occluding any data display that was there. Cycle through the menu pages by pressing F9 or quit help by pressing F10. Obtain the help message on a menu item by pressing the function key (F1-F8) corresponding to that item's menu number (1-8). The message remains in the bottom half of the main screen although the cursor returns to the command window. 4-5 GENERAL HELP The data display occluded by the help menu and messages can be restored by pressing F2. 4-6